package cn.edu.jsu.zjj.model.utils;

import cn.edu.jsu.zjj.model.entity.User;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.util.*;


public class ExcelUtils {

        public static List<User> readExcel(MultipartFile file, Long userId) {
            List<List<String>> userList = new ArrayList<>(); // 初始化 userList

            try {
                // 获取文件输入流
                InputStream inputStream = file.getInputStream();
                // 定义一个 Workbook 的变量
                Workbook workbook = null;
                // 截取文件名 . 后面的后缀名，判断是 xls 还是 xlsx
                String fileName = file.getOriginalFilename();
                String fileExtension = fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase();

                if ("xls".equals(fileExtension)) {
                    workbook = new HSSFWorkbook(inputStream);
                } else if ("xlsx".equals(fileExtension)) {
                    workbook = new XSSFWorkbook(inputStream);
                }

                // 获取第一张表
                Sheet sheet = workbook.getSheetAt(0);
                // sheet.getPhysicalNumberOfRows() 获取总的行数
                // 循环读取每一行
                for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                    // 循环读取每一个格
                    Row row = sheet.getRow(i);
                    // row.getPhysicalNumberOfCells() 获取总的列数
                    List<String> oneUser = new ArrayList<>();
                    int numberOfCells = row.getPhysicalNumberOfCells();
                    for (int index = 0; index < numberOfCells; index++) {
                        // 获取数据，但是我们获取的 cell 类型
                        Cell cell = row.getCell(index);
                        // 转换为字符串类型
                        cell.setCellType(CellType.STRING);
                        // 获取得到字符串
                        String id = cell.getStringCellValue();
                        oneUser.add(id);
                    }
                    userList.add(oneUser); // 添加到 userList 中
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return input(userList, userId);
        }



    public static List<User> input(List<List<String>> userList,Long userId){


        List<User> users = new ArrayList<>();
        for(List<String> oneUser : userList){

            User user = new User();
            // 设置默认值
            user.setUpdateTime(LocalDateTime.now());
            user.setCreateTime(LocalDateTime.now());
            user.setOperUserId(userId);
            user.setCreateUserId(userId);
            user.setIsDelete(0);
            // 设置值
            user.setNumber(oneUser.get(0));
            user.setName(oneUser.get(1));
            user.setPhoneNumber(oneUser.get(2));
            user.setEmail(oneUser.get(3));
            user.setPost(oneUser.get(4));
            user.setDegree(oneUser.get(5));
            user.setAddress(oneUser.get(6));
            if (Objects.equals(oneUser.get(7), "男")){
                user.setSex("0");
            }
            if (Objects.equals(oneUser.get(7), "女")){
                user.setSex("1");
            }
            user.setProfile(oneUser.get(8));

            users.add(user);
        }
        return users;
    }

    public static void exportWorkbookToExcel(Workbook workbook, String fileName, HttpServletResponse response) throws IOException {

        // 设置响应头，告诉浏览器这是一个要下载的文件
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");

        // 将工作簿写入到响应的输出流中
        try (OutputStream outputStream = response.getOutputStream()) {
            workbook.write(outputStream);
        } finally {
            // 关闭工作簿
            workbook.close();
        }
    }


    public static void export(HttpServletResponse response, List<List<String>> headList, List<List<Object>> dataList, List<List<Object>> indexList) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
//        String filename = URLEncoder.encode(System.currentTimeMillis() + "", "utf-8");
        String filename = "export_" + System.currentTimeMillis();
        response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");

        if (Objects.isNull(indexList) || indexList.isEmpty()) {
            // 先仅仅写入头，再以不写入头的方式写入数据
            EasyExcel.write(response.getOutputStream()).head(headList).sheet(filename).doWrite(dataList);
        } else {
            List<Integer> indexLists = new ArrayList<>();
            for (List<Object> objects : indexList) {
                for (int i = 0; i < dataList.size(); i++) {
                    if (Objects.equals(objects, dataList.get(i))) {
                        indexLists.add(i);
                    }
                }
            }
            //最后一行提示行
            indexLists.add(dataList.size());

            List<Object> remark = new ArrayList<>();
            remark.add("注：表格中绿色字体表示该数据相同，已经合并，请注意核对。");
            dataList.add(dataList.size(),remark);
            EasyExcel.write(response.getOutputStream())
                    .head(headList)
                    .sheet(filename)
                    .registerWriteHandler(new CustomCellStyleHandler(indexLists))
                    .doWrite(dataList);

        }
    }
}
